IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetUserAccessToPortfolio]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetUserAccessToPortfolio]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************
* VSS File Path: $Archive: /PHSRAG/Applications/WebApplications/Insight_Dev/Database/StoredProcedures/GetUserAccessToPortfolio.sql $
* Name:  GetUserContext
* Purpose:  
* Gets user context information based on a domain user name.
*
* PARAMETERS
* Name    				Description					
* -------------  		---------------------------------------
* @userName			The user's userName
*
* RESULT SET
* Name    			Description					
* --------------	-------------------------------------------
* User Context information				n/a
*
*
* RETURN VALUE
* Value    			Description					
* --------------	-------------------------------------------
* N/A				N/A
*
******************************************************************/
CREATE Procedure [dbo].GetUserAccessToPortfolio (
		@domainUserId	  AS INT
)
AS

BEGIN

	SET NOCOUNT ON

	DECLARE @portfolioAccess INT
	SET @portfolioAccess = 3
	DECLARE @adminAccessRole int
	set @adminAccessRole = 131072
	DECLARE @budgetBuilderAdmin int

	IF EXISTS (select ModuleRoleBitmask
				FROM FwkApplicationUser 
				WHERE FwkDomainUserId = @domainUserId
				AND (ModuleRoleBitmask & @adminAccessRole) = @adminAccessRole)
	BEGIN
		select @adminAccessRole
	END
	else
	BEGIN
		if exists ( select arm.RightBitmask as CTPortfolioRights 
					from FwkDomainUser du
					Inner Join admPerson p
					ON du.ID = p.FwkDomainUserId 
					inner join AdmRights_Map arm
					ON arm.FwkDomainUserId = du.id
					AND arm.AdmHierarchyLevelTypeId = @portfolioAccess
					where du.Id = @domainUserId )
		BEGIN
			SELECT  
			SUM (DISTINCT(aart.BitValue)) as CTPortfolioRights
			FROM
			(
				select arm.RightBitmask as CTPortfolioRights 
				from FwkDomainUser du
				Inner Join admPerson p
				ON du.ID = p.FwkDomainUserId 
				inner join AdmRights_Map arm
				ON arm.FwkDomainUserId = du.id
				AND arm.AdmHierarchyLevelTypeId = @portfolioAccess
				where du.Id = @domainUserId
			) ar
			INNER JOIN AccessRightType aart
			ON ar.CTPortfolioRights & aart.BitValue = aart.BitValue
		END
		ELSE
		BEGIN
			SELECT MAX(urc.AccessRightBitmask)
			FROM ClinicalTrialBudget b		
			INNER JOIN FwkResource_DomainUser_Map urc
			ON urc.FwkResourceId = b.FwkResourceId
			INNER JOIN FwkResource fr
			on fr.Id = urc.FwkResourceId
			AND fr.FwkResourceTypeId = 3
			WHERE urc.fwkDomainUserId = @domainUserId		
		END
	END
END
GO